- frmPartialDueList_Staff.vb
- project /
1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Public Class frmPartialDueList_Staff
4
5 Sub fillSession()
6 Try
7 con = New SqlConnection(cs)
8 con.Open()
9 adp = New SqlDataAdapter()
10 adp.SelectCommand = New SqlCommand("SELECT distinct (Session) FROM BusFeepayment_Student", con)
11 ds = New DataSet("ds")
12 adp.Fill(ds)
13 dtable = ds.Tables(0)
14 cmbSession.Items.Clear()
15 For Each drow As DataRow In dtable.Rows
16 cmbSession.Items.Add(drow(0).ToString())
17 Next
18 Catch ex As Exception
19 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
20 End Try
21 End Sub
22
23 Sub Reset()
24 cmbSession.SelectedIndex = -1
25 cmbInstallment.SelectedIndex = -1
26 cmbInstallment.Enabled = False
27 dgw.Rows.Clear()
28 fillSession()
29 End Sub
30
31 Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
32 Me.Close()
33 End Sub
34
35 Private Sub frmPartialDueList_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
36 fillSession()
37 End Sub
38
39 Private Sub Button10_Click(sender As System.Object, e As System.EventArgs) Handles Button10.Click
40 Reset()
41 End Sub
42
43 Private Sub Button9_Click(sender As System.Object, e As System.EventArgs) Handles Button9.Click
44 Dim rowsTotal, colsTotal As Short
45 Dim I, j, iC As Short
46 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
47 Dim xlApp As New Excel.Application
48 Try
49 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
50 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
51 xlApp.Visible = True
52
53 rowsTotal = dgw.RowCount
54 colsTotal = dgw.Columns.Count - 1
55 With excelWorksheet
56 .Cells.Select()
57 .Cells.Delete()
58 For iC = 0 To colsTotal
59 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
60 Next
61 For I = 0 To rowsTotal - 1
62 For j = 0 To colsTotal
63 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
64 Next j
65 Next I
66 .Rows("1:1").Font.FontStyle = "Bold"
67 .Rows("1:1").Font.Size = 12
68
69 .Cells.Columns.AutoFit()
70 .Cells.Select()
71 .Cells.EntireColumn.AutoFit()
72 .Cells(1, 1).Select()
73 End With
74 Catch ex As Exception
75 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
76 Finally
77 'RELEASE ALLOACTED RESOURCES
78 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
79 xlApp = Nothing
80 End Try
81 End Sub
82
83 Private Sub TabControl1_Click(sender As System.Object, e As System.EventArgs) Handles TabControl1.Click
84 Reset()
85 End Sub
86
87 Private Sub Button11_Click(sender As System.Object, e As System.EventArgs) Handles Button11.Click
88 Try
89 If Len(Trim(cmbSession.Text)) = 0 Then
90 MessageBox.Show("Please select session", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
91 cmbSession.Focus()
92 Exit Sub
93 End If
94 con = New SqlConnection(cs)
95 con.Open()
96 cmd = New SqlCommand("SELECT RTRIM(Staff.StaffID),RTRIM(Staffname),RTRIM(Designation),RTRIM(BusCardHolder_Staff.Location),RTRIM(SchoolName),RTRIM(PaymentDue) FROM BusFeePayment_Staff,Staff,BusCardHolder_Staff,SchoolInfo where Staff.ST_ID=BusCardHolder_Staff.StaffID and BusFeePayment_Staff.BusHolderID=BusCardHolder_Staff.BCH_ID and SchoolInfo.S_ID=Staff.schoolID and BusFeePayment_Staff.Session=@d1 and Installment=@d2 and PaymentDue > 0 order by StaffName", con)
97 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
98 cmd.Parameters.AddWithValue("@d2", cmbInstallment.Text)
99 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
100 dgw.Rows.Clear()
101 While (rdr.Read() = True)
102 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5))
103 End While
104 con.Close()
105 Catch ex As Exception
106 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
107 End Try
108 End Sub
109
110
111 Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
112 Cursor = Cursors.Default
113 Timer1.Enabled = False
114 End Sub
115
116
117 Private Sub Button8_Click(sender As System.Object, e As System.EventArgs) Handles Button8.Click
118 Try
119 If Len(Trim(cmbSession.Text)) = 0 Then
120 MessageBox.Show("Please select session", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
121 cmbSession.Focus()
122 Exit Sub
123 End If
124 Cursor = Cursors.WaitCursor
125 Timer1.Enabled = True
126 Dim rpt As New rptBusFeePartialDue_Student 'The report you created.
127 Dim myConnection As SqlConnection
128 Dim MyCommand As New SqlCommand()
129 Dim myDA As New SqlDataAdapter()
130 Dim myDS As New DataSet 'The DataSet you created.
131 myConnection = New SqlConnection(cs)
132 MyCommand.Connection = myConnection
133 MyCommand.CommandText = "SELECT BusFeePayment_Staff.Id, BusFeePayment_Staff.BFP_ID, BusFeePayment_Staff.PaymentID, BusFeePayment_Staff.BusHolderID, BusFeePayment_Staff.Session, BusFeePayment_Staff.Installment,BusFeePayment_Staff.TotalFee, BusFeePayment_Staff.DiscountPer, BusFeePayment_Staff.DiscountAmt, BusFeePayment_Staff.PreviousDue, BusFeePayment_Staff.Fine, BusFeePayment_Staff.GrandTotal, BusFeePayment_Staff.TotalPaid, BusFeePayment_Staff.ModeOfPayment, BusFeePayment_Staff.PaymentModeDetails, BusFeePayment_Staff.PaymentDate, BusFeePayment_Staff.PaymentDue,BusCardHolder_Staff.BCH_ID, BusCardHolder_Staff.StaffID, BusCardHolder_Staff.BusNo, BusCardHolder_Staff.Location, BusCardHolder_Staff.JoiningDate, BusCardHolder_Staff.Status, Staff.St_ID,Staff.StaffID AS Expr1, Staff.StaffName, Staff.DateOfJoining, Staff.Gender, Staff.FatherName, Staff.TemporaryAddress, Staff.PermanentAddress, Staff.Designation, Staff.Qualifications, Staff.DOB, Staff.PhoneNo,Staff.MobileNo, Staff.Photo, Staff.ClassType, Staff.SchoolID, Staff.AccountName, Staff.AccountNumber, Staff.Bank, Staff.Branch, Staff.IFSCcode, Staff.Salary, SchoolInfo.S_Id,SchoolInfo.SchoolName, SchoolInfo.Address, SchoolInfo.ContactNo, SchoolInfo.AltContactNo, SchoolInfo.FaxNo, SchoolInfo.Email , SchoolInfo.Website, SchoolInfo.Logo, SchoolInfo.RegistrationNo,SchoolInfo.DiseNo, SchoolInfo.IndexNo, SchoolInfo.EstablishedYear, SchoolInfo.Class, SchoolInfo.SchoolType FROM BusFeePayment_Staff INNER JOIN BusCardHolder_Staff ON BusFeePayment_Staff.BusHolderID = BusCardHolder_Staff.BCH_ID INNER JOIN Staff ON BusCardHolder_Staff.StaffID = Staff.St_ID INNER JOIN SchoolInfo ON Staff.SchoolID = SchoolInfo.S_Id where BusFeePayment_Staff.Session=@d1 and PaymentDue > 0 order by StaffName"
134 MyCommand.Parameters.AddWithValue("@d1", cmbSession.Text)
135 MyCommand.CommandType = CommandType.Text
136 myDA.SelectCommand = MyCommand
137 myDA.Fill(myDS, "Staff")
138 myDA.Fill(myDS, "BusCardHolder_Staff")
139 myDA.Fill(myDS, "SchoolInfo")
140 myDA.Fill(myDS, "BusFeePayment_Staff")
141 rpt.SetDataSource(myDS)
142 frmReport.CrystalReportViewer1.ReportSource = rpt
143 frmReport.ShowDialog()
144 Catch ex As Exception
145 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
146 End Try
147 End Sub
148
149 Private Sub cmbSession_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbSession.SelectedIndexChanged
150 Try
151 cmbInstallment.Enabled = True
152 con = New SqlConnection(cs)
153 con.Open()
154 Dim ct As String = "SELECT distinct RTRIM(Installment) FROM BusFeePayment_Staff,Staff,BusCardHolder_Staff where Staff.St_ID=BusCardHolder_Staff.StaffID and BusFeePayment_Staff.BusHolderID=BusCardHolder_Staff.BCH_ID and BusFeePayment_Staff.Session=@d1"
155 cmd = New SqlCommand(ct)
156 cmd.Connection = con
157 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
158 rdr = cmd.ExecuteReader()
159 cmbInstallment.Items.Clear()
160 While rdr.Read
161 cmbInstallment.Items.Add(rdr(0))
162 End While
163 con.Close()
164 Catch ex As Exception
165 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
166 End Try
167 End Sub
168 End Class
